package org.bhujmandir.android.utils;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

import org.bhujmandir.android.R;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHandle extends SQLiteOpenHelper{

	public static final int DBVERSION = 1;
	public static final String DB_NAME = "media";
	private Context context;
	SQLiteDatabase mediaDB;
	
	public DBHandle(Context parentContext){
		super(parentContext, DB_NAME, null, DBVERSION);
		this.context = parentContext;
	}
	
	private void csvToDB(SQLiteDatabase db){
		//Get CSV File
		InputStream csvStream = context.getResources().openRawResource(R.raw.media);
		BufferedReader csvReader = new BufferedReader(new InputStreamReader(csvStream));
		
		
		String data = "";
		boolean firstLine = true;
		String tableName = "media";
		String columns = "type,publisher,album,artist,title,track,url,size,mtime,allowDownload";
		String insert1String = "INSERT INTO " + tableName + " (" + columns + ") values(";
		String insert2String = ");";
		
		db.beginTransaction();
		
		try{
			while((data = csvReader.readLine()) != null){
				if(!firstLine){
					StringBuilder queryString = new StringBuilder(insert1String);
					String[] sarray = data.split(",");
					for(int i=0;i<9;i++){
						queryString.append("'"+sarray[i]+"',");
					}
					queryString.append("'"+sarray[9]+"'");
					queryString.append(insert2String);
					db.execSQL(queryString.toString());
				}
				firstLine = false;
			}
			
		}catch (Exception e) {
			
			// TODO: handle exception
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		//mediaDB = this.getWritableDatabase();
		String queryString = "CREATE TABLE media (_id INTEGER PRIMARY KEY, type TEXT, publisher TEXT, album TEXT, artist TEXT, title TEXT, track TEXT, url TEXT, size TEXT, mtime TEXT, allowDownload TEXT)";
		db.execSQL(queryString);
		csvToDB(db);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS media");
		onCreate(db);
	}
	
	@Override
	public synchronized void close() {
		super.close();
	}
	
}
